package net.wanho.commons.util;


import net.wanho.commons.exception.DataAccessException;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.log4j.Logger;

import java.sql.SQLException;
import java.util.HashMap;


public class JDBCTemplate {
    private static final Logger LOGGER = Logger.getLogger(JDBCTemplate.class);

    /**
     * 新增
     * @param sql 执行的SQL
     * @param params  SQL ? 替换的值
     * @return 自增长的id
     * @throws DataAccessException
     */
    public int save(String sql, Object... params) throws DataAccessException {
        try {
            LOGGER.debug("执行插入的SQL==================>" + sql);
            LOGGER.debug("执行插入SQL参数================>" + ArrayUtils.toString(params));
            return new QueryRunner().insert(JDBCUtil.getConnection(), sql, new ScalarHandler<Long>(), params).intValue();
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("执行插入异常:" + e.getMessage());
            throw new DataAccessException("数据保存异常");
        }
    }

    public void update(String sql, Object... params) throws DataAccessException {
        try {
            LOGGER.debug("执行修改的SQL==================>" + sql);
            LOGGER.debug("执行修改SQL参数================>" + ArrayUtils.toString(params));
            new QueryRunner().update(JDBCUtil.getConnection(), sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("执行修改异常:" + e.getMessage());
            throw new DataAccessException("数据更新异常");
        }
    }

    public void delete(String sql, Object... params) throws DataAccessException {
        try {
            LOGGER.debug("执行删除的SQL==================>" + sql);
            LOGGER.debug("执行删除SQL参数================>" + ArrayUtils.toString(params));
            new QueryRunner().update(JDBCUtil.getConnection(), sql, params);
        } catch (SQLException e) {
            e.printStackTrace();
            LOGGER.error("执行删除异常:" + e.getMessage());
            throw new DataAccessException("数据删除异常");
        }
    }

    /**
     * 查询 返回多行数据
     *
     * @param sql
     * @param cls
     * @param params
     * @param <T>
     * @return
     * @throws DataAccessException
     */
    public <T> T queryForList(String sql, Class cls, Object... params) throws DataAccessException {
        try {
            LOGGER.debug("执行查询queryForList的SQL==================>" + sql);
            LOGGER.debug("执行查询queryForListS 参数================>" + ArrayUtils.toString(params));
            return (T) new QueryRunner().query(JDBCUtil.getConnection(), sql, new BeanListHandler<>(cls), params);
        } catch (Exception e) {
            e.printStackTrace();
			LOGGER.error("执行查询queryForList异常:" + e.getMessage());
            throw new DataAccessException("数据查询异常");
        }
    }


	/**
	 * 查询返回 单行数据
	 * @param sql
	 * @param cls
	 * @param params
	 * @param <T>
	 * @return
	 * @throws DataAccessException
	 */
	public <T> T queryForObject(String sql, Class cls, Object... params) throws DataAccessException {
		try {
			LOGGER.debug("执行查询queryForObject的SQL==================>" + sql);
			LOGGER.debug("执行查询queryForObject   参数================>" + ArrayUtils.toString(params));
			return (T) new QueryRunner().query(JDBCUtil.getConnection(), sql, new BeanHandler<>(cls), params);
		} catch (Exception e) {
			e.printStackTrace();
			LOGGER.error("queryForObject异常:" + e.getMessage());
			throw new DataAccessException("数据查询异常");
		}
	}


    public <T> T queryForMap(String sql, Object... params) throws DataAccessException {
        try {
            LOGGER.debug("执行查询queryForObject的SQL==================>" + sql);
            LOGGER.debug("执行查询queryForObject   参数================>" + ArrayUtils.toString(params));
            return (T) new QueryRunner().query(JDBCUtil.getConnection(), sql, new MapHandler(), params);
        } catch (Exception e) {
            e.printStackTrace();
            LOGGER.error("queryForObject异常:" + e.getMessage());
            throw new DataAccessException("数据查询异常");
        }
    }

	/**
	 * 查询 满足条件的总记录数
	 * @param sql
	 * @param params
	 * @return
	 * @throws DataAccessException
	 */
	public int queryForInt(String sql,  Object... params) throws DataAccessException {
		try {
			LOGGER.debug("执行查询queryForInt的SQL==================>" + sql);
			LOGGER.debug("执行查询queryForInt   参数================>" + ArrayUtils.toString(params));
			return new QueryRunner().query(JDBCUtil.getConnection(), sql, new ScalarHandler<Long>(), params).intValue();
		} catch (Exception e) {
			e.printStackTrace();
			LOGGER.error("执行查询queryForInt异常:" + e.getMessage());
			throw new DataAccessException("数据查询异常");
		}
	}
}
